package com.puhui.chome.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.puhui.chome.model.vo.QueryData;

/**
 * 调用数据仓库JDBC
 * 
 * @author dingxingang
 */
public class JdbcUtil {

    private static final Logger logger = LoggerFactory.getLogger(JdbcUtil.class);

    private static Connection conn = null;
    private static PreparedStatement pstmt = null;
    private static ResultSet rs = null;

    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static String URL = "";
    public static String DB_NAME = "";
    public static String DB_PASS = "";

    /**
     * @author lihao
     */
    private JdbcUtil() {
        super();
    }

    // 创建连接
    public static Connection getConn() {
        try {
            Class.forName(DRIVER); // 注册驱动
            conn = DriverManager.getConnection(URL, DB_NAME, DB_PASS); // 获得数据库连接
        } catch (Exception e) {
            logger.error("加载数据库连接异常!", e);
        }
        return conn; // 返回连接
    }

    // 关闭连接
    public static void claseConn() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error("关闭jdbc驱动conn异常", e);
            }
        }
    }

    // 关闭驱动 结果集 数据库操作
    private static void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                logger.error("关闭jdbc驱动rs异常", e);
            }
        }

        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                logger.error("关闭jdbc驱动psmt异常", e);
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error("关闭jdbc驱动conn异常", e);
            }
        }
    }

    /**
     * 执行sql
     * 
     * @param preparedSql
     * @param param
     * @return
     */
    public static int executeSQL(String preparedSql, String[] param) {
        int num = 0;
        /* 处理SQL,执行SQL */
        try {
            conn = getConn(); // 得到数据库连接
            pstmt = conn.prepareStatement(preparedSql); // 得到PreparedStatement对象
            pstmt.setQueryTimeout(120);
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    pstmt.setString(i + 1, param[i]); // 为预编译sql设置参数
                }
            }
            num = pstmt.executeUpdate(); // 执行SQL语句
        } catch (Exception e) {
            logger.error("执行Sql异常", e); // 处理SQLException异常
        } finally {
            closeAll(conn, pstmt, null); // 释放资源
        }
        return num;
    }

    /**
     * 查询数据
     * 
     * @param sql
     * @param names
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> queryData(String sql, String[] names) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> data = new HashMap<>();
                for (String name : names) {
                    Object value = rs.getString(name);
                    data.put(name, value);
                }
                list.add(data);
            }
        } catch (SQLException e) {
            logger.error("执行sql异常！", e);
        } finally {
            closeAll(conn, pstmt, rs);
        }
        return list;
    }

    /**
     * 查询数据数量
     * 
     * @param sql
     * @return 查询记录数
     * @throws Exception
     */
    public static Long queryDataCount(String sql) {
        Long count = 0l;
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count = Long.parseLong(rs.getString("number"));
                break;
            }
        } catch (Exception e) {
            logger.error("执行sql异常！" + e.getMessage(), e);
            count = null;
        } finally {
            closeAll(conn, pstmt, rs);
        }
        return count;
    }

    /**
     * 查询数据数量
     * 
     * @param sql
     * @param 数据库连接
     * @return 查询记录数
     * @throws Exception
     */
    public static Long queryDataCount(String sql, Connection connection) {
        Long count = 0l;
        try {
            conn = connection;
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count = Long.parseLong(rs.getString("number"));
                break;
            }
        } catch (SQLException e) {
            logger.error("执行sql异常！" + e.getMessage(), e);
            count = null;
        } finally {
            closeAll(null, pstmt, rs);
        }
        return count;
    }

    /**
     * 查询数据
     * 
     * @param sql
     * @param names
     * @return
     * @throws Exception
     */
    public static QueryData queryData(String sql) {
        QueryData data = new QueryData();
        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获取查询列
            List<String> fieldList = new ArrayList<>();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                fieldList.add(rsmd.getColumnName(i));
            }
            data.setFieldList(fieldList);
            // 遍历获取数据
            List dataList = new ArrayList<>();
            while (rs.next()) {
                Map map = new HashMap(rsmd.getColumnCount());
                for (String columnName : fieldList) {
                    map.put(columnName, rs.getString(columnName));
                }
                dataList.add(map);
            }
            data.setDataList(dataList);
        } catch (SQLException e) {
            logger.error("执行sql异常！", e);
            data = null;
        } finally {
            closeAll(conn, pstmt, rs);
            return data;
        }
    }

    public static void main(String[] args) {
        String sql = "SELECT t.`request_id`, t.`main_id`, t.`overdue_status`, t.`now_overdue_status`, t.`status` FROM repay_case_manage t WHERE t.`customer_id` = 86124 ";
        QueryData data = queryData(sql);
        System.out.println(data.getDataList().size());
    }

}
